---
title: "SQL-002: Hazard group retrieve"
summary: Retrieve hazard groups effective on a given date
status: draft
owner: SAIF Claims BOF team
last_reviewed: 2026-08-21
library: SAIF.Business.Policy
reference_type: embedded
---

# SQL-002 Hazard group retrieve

Retrieves all `HAZARD_GROUP` rows whose effective/expiration date window contains the supplied effective date, used to populate `clsHazardGroupCollection` via the standard BOF retrieve lifecycle.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-002 |
| **Operation type** | Read |
| **Execution method** | Inline SQL, StringBuilder-composed via `BuildWhere()` |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsHazardGroupCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` / `AfterRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:EffectiveDate` | Input | Date (`MyCriteria.EffectiveDate`) | N/A | Effective date used to filter hazard groups whose `EFFECTIVE_DATE`/`EXPIRATION_DATE` window contains it. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [HAZARD_GROUP](../database-tables.md#hazard_group) | SELECT | `HAZARD_GROUP_ID`, `HAZARD_GROUP_SHORT_DESCR`, `HAZARD_GROUP_LONG_DESCR`, `EFFECTIVE_DATE`, `EXPIRATION_DATE`, `CREATE_DATE`, `CREATE_USER`, `UPDATE_DATE`, `UPDATE_USER`, `NCCI_HAZARD_GROUP_CODE` |

## Sample SQL

```sql
SELECT
  H.HAZARD_GROUP_ID,
  H.HAZARD_GROUP_SHORT_DESCR,
  H.HAZARD_GROUP_LONG_DESCR,
  H.EFFECTIVE_DATE,
  H.EXPIRATION_DATE,
  H.CREATE_DATE,
  H.CREATE_USER,
  H.UPDATE_DATE,
  H.UPDATE_USER,
  H.NCCI_HAZARD_GROUP_CODE
FROM HAZARD_GROUP H
WHERE :EffectiveDate >= H.EFFECTIVE_DATE
  AND :EffectiveDate < NVL(H.EXPIRATION_DATE, :EffectiveDate + 1)
```

Reconstructed from `String &` concatenation for the SELECT list and `BuildWhere()` for the WHERE clause.

## Operations and processes
- [Retrieve hazard groups effective on a date](../operations/retrieve-hazard-groups-effective-on-a-date.md)

## Sources
- `SAIF.Business.Policy\clsHazardGroupCollection.vb`